package com.snsoft.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.snsoft.bean.User;
import com.snsoft.util.EncodeUtils;
import com.snsoft.util.JdbcUtils;

public class DutyDao {
	/**
	 * 添加用户的dao
	 * 
	 * @param user
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Object> adduser(User user) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql1 = "INSERT INTO users(userCode,userName,userPassWord,role,operate,deleteFlag)VALUES(?,?,?,?,?,1);";
		String sql2 = "SELECT userID,userCode,userPassWord FROM users WHERE userCode=? AND deleteFlag=1;";
		String password = "123456";
		runner.update(sql1, new Object[] { user.getUserCode(), user.getUserName(), EncodeUtils.encodeByMd5(password),
				user.getRole(), user.getOperate() });
		return runner.query(sql2, new MapHandler(), user.getUserCode());
	}

	/**
	 * 删除用户的dao
	 * 
	 * @param id
	 * @throws SQLException
	 */
	public int deleteuser(int id) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "UPDATE `users` SET `deleteFlag`=0 WHERE userID=?;";
		return runner.update(sql, id);
	}

	/**
	 * 获取用户所属或管理的所有基地Id，并进行去重复处理
	 * 
	 * @param userId
	 * @return
	 * @throws SQLException
	 */
	public List<Integer> getUserCompanyIds(int userId) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "SELECT companyID FROM `usergreenhouse` WHERE userID=? GROUP BY companyID;";
		List<Integer> companyids = runner.query(sql, new ColumnListHandler<Integer>(), userId);
		return companyids;
	}

	/**
	 * 获取指定基地ID的所有普通用户列表
	 * 
	 * @param companyID
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> getUsers(String companyID) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "SELECT gh.userID, users.userName FROM `usergreenhouse` gh LEFT JOIN users "
				+ "ON gh.userID = users.userID WHERE companyID=? AND users.role=0 AND users.deleteFlag=1;";
		List<Map<String, Object>> users = runner.query(sql, new MapListHandler(), companyID);
		return users;
	}

	/**
	 * 获取独立用户列表
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> getIndependentUser() throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "SELECT users.userID, users.userName FROM users LEFT JOIN usergreenhouse "
				+ "ON users.userID = usergreenhouse.userID WHERE users.deleteFlag=1 "
				+ "AND users.role=0 AND ISNULL(usergreenhouse.userID);";
		List<Map<String, Object>> users = runner.query(sql, new MapListHandler());
		return users;
	}

	/**
	 * 设置温室操作权限
	 * 
	 * @param userID
	 * @param operate
	 * @return
	 * @throws SQLException
	 */
	public int setOperate(String userID, String operate) throws SQLException {
		QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "UPDATE users SET operate=? WHERE userID=?;";
		return runner.update(sql, new Object[] { operate, userID });
	}
}
